import shutil
from configparser import ConfigParser
from openpyxl import load_workbook

import tools_pmc
from tools_pmc import (today_str1,
                       lj_kcmxz, lj_gdwcqk_y,
                       lj_kcmxz_yesterday, lj_kcmxz_today_y, lj_kcmxz_today
                       )


def vlookup(lookup_value, table_array, col_index_num):
    global a
    for row in table_array:
        if row[0].value == lookup_value:
            a = table_array.index(row) - 1
            return row[col_index_num - 1].value


wb_bom = load_workbook(tools_pmc.lj_bom)
ws_bom = wb_bom.worksheets[0]

wb_gdwcqk = load_workbook(lj_gdwcqk_y)
ws_gdwcqk = wb_gdwcqk.active

wb_kcmxz_today = load_workbook(lj_kcmxz_today_y)
ws_kcmxz_today = wb_kcmxz_today.worksheets[0]

wb_kcmxz = load_workbook(lj_kcmxz_yesterday)
ws_kcmxz = wb_kcmxz.worksheets[0]

# 移植工单完成表处理程序
ws_gdwcqk.insert_cols(3)
for i in range(2, ws_gdwcqk.max_row + 1):
    ws_gdwcqk.cell(i, 3).value = ws_gdwcqk.cell(i, 1).value + "-" + ws_gdwcqk.cell(i, 2).value

table_array = list(ws_gdwcqk['C1':'H' + str(ws_gdwcqk.max_row)])
for i in range(2, ws_kcmxz_today.max_row + 1):
    ws_kcmxz_today.cell(i, 5).value = tools_pmc.detect_date(ws_kcmxz_today.cell(i, 5).value, 1)
    global a
    a = 0
    if ws_kcmxz_today.cell(i, 14).value is not None:
        try:
            if len(ws_kcmxz_today.cell(i, 14).value) != 0 and ws_kcmxz_today.cell(i, 14).value[0] == "5":
                if vlookup(ws_kcmxz_today.cell(i, 14).value, table_array, 2)[0] == "3" \
                        or vlookup(ws_kcmxz_today.cell(i, 14).value, table_array, 2)[0] == "1":
                    ws_kcmxz_today.cell(i, 16).value = vlookup(ws_kcmxz_today.cell(i, 14).value, table_array, 2)
                    ws_kcmxz_today.cell(i, 17).value = vlookup(ws_kcmxz_today.cell(i, 14).value, table_array, 3)
                    ws_kcmxz_today.cell(i, 18).value = vlookup(ws_kcmxz_today.cell(i, 14).value, table_array, 4)
                    ws_kcmxz_today.cell(i, 19).value = vlookup(ws_kcmxz_today.cell(i, 14).value, table_array, 6)
                else:
                    while table_array[a][1].value[0] != '3':
                        a -= 1
                    sn = table_array[a][1].value
                    if (ws_kcmxz_today.cell(i, 1).value in tools_pmc.bom_1(sn, ws_bom)[0]
                            or ws_kcmxz_today.cell(i, 1).value in tools_pmc.bom_1(sn, ws_bom)[2]):
                        ws_kcmxz_today.cell(i, 16).value = table_array[a][1].value
                        ws_kcmxz_today.cell(i, 17).value = table_array[a][2].value
                        ws_kcmxz_today.cell(i, 18).value = table_array[a][3].value
                        ws_kcmxz_today.cell(i, 19).value = table_array[a][5].value
                    else:
                        ws_kcmxz_today.cell(i, 16).value = "BOM检测未通过，请手动查询！"
        except:
            ws_kcmxz_today.cell(i, 16).value = "error:404"

for i in range(ws_kcmxz_today.max_row, 1, -1):
    if ws_kcmxz_today.cell(i, 5).value is None or ws_kcmxz_today.cell(i, 5).value == "":
        ws_kcmxz_today.delete_rows(i)

a = ws_kcmxz.max_row + 1
for i in range(2, ws_kcmxz_today.max_row + 1):
    for j in range(1, 20):
        ws_kcmxz.cell(a, j).value = ws_kcmxz_today.cell(i, j).value
    ws_kcmxz.cell(a, 5).number_format = 'yyyy/mm/dd'
    a += 1

range_active = "A1:S" + str(ws_kcmxz.max_row)
ws_kcmxz.auto_filter.ref = range_active

ws_kcmxz.title = "库存明细账" + today_str1
wb_kcmxz_today.save(lj_kcmxz_today)
wb_kcmxz.save(lj_kcmxz)

lj_config = "//192.168.70.101/19计划管理部/01.计划/1，数据处理工具/Tools/PMCtools_Python_v2.0/dist/config.ini"
cfg = ConfigParser()
cfg.read(lj_config)
if not cfg.get('set', 'Test mode'):
    shutil.copy(lj_kcmxz,
                "//192.168.70.101/19计划管理部/01.计划/1，数据处理工具")
print(today_str1 + ":kcmxz succeed!")
